const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const db = new Database(DB_PATH);

console.log('🔧 修复table_c14缺失的用途字段');
console.log(`🗃️  数据库路径: ${DB_PATH}`);
console.log('=====================================');

// 检查数据库中的table_c表
console.log('\n📋 检查现有table_c表:');
const allTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'table_c%' ORDER BY name").all();
console.log(`  找到 ${allTables.length} 个table_c表`);
allTables.slice(0, 10).forEach(table => console.log(`    ${table.name}`));
if (allTables.length > 10) console.log(`    ... 还有 ${allTables.length - 10} 个表`);

// 检查table_c14是否存在
const table14Exists = allTables.some(t => t.name === 'table_c14');
console.log(`\n🔍 table_c14存在: ${table14Exists ? '✅' : '❌'}`);

// 1. 检查当前表结构
let tableInfo = [];
if (table14Exists) {
  console.log('\n📋 当前table_c14结构:');
  tableInfo = db.prepare("PRAGMA table_info(table_c14)").all();
} else {
  console.log('\n⚠️  table_c14不存在，需要创建');
}

if (table14Exists) {
  tableInfo.forEach(col => {
    console.log(`  ${col.cid}. ${col.name} (${col.type}) ${col.notnull ? 'NOT NULL' : ''} ${col.dflt_value ? `DEFAULT ${col.dflt_value}` : ''}`);
  });

  // 2. 检查当前数据
  console.log('\n📊 当前数据行数:');
  const rowCount = db.prepare("SELECT COUNT(*) as count FROM table_c14").get();
  console.log(`  总行数: ${rowCount.count}`);
}

// 3. 检查字段元数据
console.log('\n📋 当前字段元数据:');
try {
  const fieldMetadata = db.prepare(`
    SELECT field_name, field_column, field_sequence, data_source, data_type 
    FROM parameter_field_metadata 
    WHERE table_name = 'table_c14' 
    ORDER BY field_sequence
  `).all();
  
  fieldMetadata.forEach(field => {
    console.log(`  ${field.field_sequence}. ${field.field_name} → ${field.field_column} (${field.data_type}) [${field.data_source}]`);
  });
} catch (error) {
  console.log('  无法查询字段元数据:', error.message);
}

// 4. 开始修复
console.log('\n🔧 开始修复...');

// 备份现有数据（如果有的话）
console.log('📦 备份现有数据...');
let existingData = [];
if (table14Exists) {
  try {
    existingData = db.prepare("SELECT * FROM table_c14").all();
    console.log(`  备份了 ${existingData.length} 行数据`);
  } catch (error) {
    console.log('  无现有数据需要备份');
  }
} else {
  console.log('  表不存在，无需备份');
}

try {
  // 5. 删除旧表
  console.log('🗑️  删除旧表...');
  db.exec("DROP TABLE IF EXISTS table_c14");
  
  // 6. 重新创建表，包含正确的字段
  console.log('🏗️  重新创建表...');
  const createTableSQL = `
    CREATE TABLE table_c14 (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      规格 TEXT,
      型号 TEXT,
      制造厂家 TEXT,
      供货厂家 TEXT,
      供货类型 TEXT,
      用途 TEXT,
      created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
    )
  `;
  
  db.exec(createTableSQL);
  console.log('✅ 表重新创建完成');
  
  // 7. 恢复数据（排除错误的'_'字段）
  if (existingData.length > 0) {
    console.log('📥 恢复数据...');
    const insertStmt = db.prepare(`
      INSERT INTO table_c14 (规格, 型号, 制造厂家, 供货厂家, 供货类型, 用途, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);
    
    let restored = 0;
    for (const row of existingData) {
      try {
        insertStmt.run(
          row.规格 || null,
          row.型号 || null, 
          row.制造厂家 || null,
          row.供货厂家 || null,
          row.供货类型 || null,
          null, // 用途字段为空，等待用户填写
          row.created_at || new Date().toISOString(),
          new Date().toISOString()
        );
        restored++;
      } catch (error) {
        console.log(`    跳过问题行: ${error.message}`);
      }
    }
    console.log(`✅ 恢复了 ${restored} 行数据`);
  }
  
  // 8. 更新字段元数据
  console.log('📝 更新字段元数据...');
  
  // 删除旧的元数据
  db.prepare("DELETE FROM parameter_field_metadata WHERE table_name = 'table_c14'").run();
  
  // 插入正确的字段元数据
  const fieldMetadataInsert = db.prepare(`
    INSERT INTO parameter_field_metadata 
    (table_name, table_code, table_description, field_sequence, field_name, field_column, example_description, data_type, measurement_class, data_source, is_required, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
  `);
  
  const fields = [
    {seq: 1, name: '规格', type: '字符型', source: 'E', desc: '—'},
    {seq: 2, name: '型号', type: '字符型', source: 'P', desc: '—'},
    {seq: 3, name: '制造厂家', type: '字符型', source: 'P', desc: '—'},
    {seq: 4, name: '供货厂家', type: '字符型', source: 'P', desc: '—'},
    {seq: 5, name: '供货类型', type: '字符型', source: 'E', desc: '如成橇、成套……'},
    {seq: 6, name: '用途', type: '字符型', source: 'E', desc: '如调压橇、计量橇、自用气橇、密度计橇其他'}
  ];
  
  fields.forEach(field => {
    fieldMetadataInsert.run(
      'table_c14',
      'BAS008',
      '橇基础参数表',
      field.seq,
      field.name,
      field.name,
      field.desc,
      field.type,
      '—',
      field.source,
      0
    );
  });
  
  console.log('✅ 字段元数据更新完成');
  
  // 9. 验证修复结果
  console.log('\n✅ 修复完成！验证结果:');
  
  const newTableInfo = db.prepare("PRAGMA table_info(table_c14)").all();
  console.log('\n📋 修复后表结构:');
  newTableInfo.forEach(col => {
    console.log(`  ${col.cid}. ${col.name} (${col.type})`);
  });
  
  const newRowCount = db.prepare("SELECT COUNT(*) as count FROM table_c14").get();
  console.log(`\n📊 数据行数: ${newRowCount.count}`);
  
  const newFieldMetadata = db.prepare(`
    SELECT field_name, field_column, field_sequence, data_source 
    FROM parameter_field_metadata 
    WHERE table_name = 'table_c14' 
    ORDER BY field_sequence
  `).all();
  
  console.log('\n📋 字段元数据:');
  newFieldMetadata.forEach(field => {
    console.log(`  ${field.field_sequence}. ${field.field_name} [${field.data_source}]`);
  });
  
  console.log('\n🎉 table_c14修复成功！');
  console.log('   ✅ 删除了错误的"_"字段');
  console.log('   ✅ 添加了缺失的"用途"字段');
  console.log('   ✅ 更新了字段元数据');
  
} catch (error) {
  console.error('❌ 修复失败:', error);
  
  // 尝试回滚
  if (existingData.length > 0) {
    console.log('🔄 尝试回滚...');
    // 可以在这里添加回滚逻辑
  }
} finally {
  db.close();
}